﻿CREATE PROCEDURE [dbo].[Report_CategoryComparisonByDate]	
@RangeReviewId int,
 @CategoryId smallint,
 @ExchangeRate smallint = 0

AS

declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @endDatePY DATE = DATEADD(Month, @totalMonths - 1, @startDatePY)
declare @totalStores INT = (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)

begin try drop table #exchangeRates; end try begin catch end catch;

Select 
	months.Date
	, CASE 
		WHEN @ExchangeRate = 0 THEN 1 --Moneda Local
		WHEN @ExchangeRate = 1 THEN ISNULL(ex.Rate,1) --Rate para Average
		WHEN @ExchangeRate = 2 THEN ISNULL(ex.ForexRate,1) --Rate para Same Store
		ELSE 1
	END AS Rate
Into #exchangeRates
From Months months
INNER JOIN dbo.RangeReviews rr WITH (NOLOCK) on rr.Id = @RangeReviewId
INNER JOIN dbo.Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
LEFT OUTER JOIN dbo.CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = months.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
Where months.Date BETWEEN @startDatePY AND @endDatePY OR months.Date BETWEEN @startDate AND @endDate


--TODO: incluir atributos de articles
SELECT	
	ar.Id
	, ar.Code
	, ar.Name
	, ar.Brand	
	, ar.Delist
	, ar.IsDelisted
	, ar.IsDeletedRangeReview
	, ar.IsNew
	, parentCat.Name 'Category'
	, cat.Name 'Subcategory'
	, cat.Code 'MC'
	, CASE WHEN umc.Id IS NOT NULL THEN ISNULL(SUM(cast(Units as money) / umc.Value) ,0) / @totalStores ELSE ISNULL(SUM(cast(Units as money)), 0) / @totalStores END AS Units	
	, CASE WHEN co.IsVATIncluded = 0 THEN ISNULL(SUM(Sales * ex.Rate), 0) / @totalStores ELSE ISNULL(SUM(Sales / (1 + ar.TaxRate) * ex.Rate), 0) / @totalStores END AS Sales	
	, CASE WHEN am.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate), 0) * am.Margin / @totalStores
		   WHEN scm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate), 0) * scm.Margin / @totalStores
		   WHEN cm.Margin is not null THEN ISNULL(SUM(Sales * ex.Rate), 0) * cm.Margin / @totalStores
		   ELSE ISNULL(SUM(GM * ex.Rate), 0) / @totalStores END AS GM	
	, sales.Date
	, CASE WHEN sales.Date < @startDate THEN 1 ELSE 0 END AS IsPriorYear
FROM Articles ar WITH (NOLOCK)																																	
	INNER JOIN Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id 
							and (@CategoryId = 0 OR ar.CategoryId = @CategoryId OR cat.ParentId = @CategoryId) 
	INNER JOIN Categories parentCat WITH (NOLOCK) on cat.ParentId = parentCat.Id and parentCat.ParentId = 6
	INNER JOIN RangeReviews rr WITH (NOLOCK) on ar.RangeReviewId = rr.Id
	INNER JOIN Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
	INNER JOIN Countries co WITH (NOLOCK) on rr.CountryId = co.Id
	INNER JOIN ArticleSales sales WITH (NOLOCK) on ar.Id = sales.ArticleId 
		AND ((sales.Date BETWEEN @startDatePY AND DATEADD(MONTH, @totalMonths - 1, @startDatePY)) OR sales.Date BETWEEN @startDate AND @endDate) 
		AND sales.Confirmed = 1 
		AND sales.StoreId in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
	--LEFT JOIN CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = sales.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
	INNER JOIN #exchangeRates ex on sales.Date = ex.Date
	LEFT JOIN UnitOfMeasureConvertions umc WITH (NOLOCK) on ar.UnitOfMeasureConvertionId = umc.Id and ar.UOM = umc.SourceUnit
	LEFT JOIN TheoreticalMargins am WITH (NOLOCK)  on am.CountryId = co.Id AND  am.ArticleId = ar.Id  
	LEFT JOIN TheoreticalMargins scm WITH (NOLOCK) on scm.CountryId = co.Id AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
	LEFT JOIN TheoreticalMargins cm WITH (NOLOCK)  on cm.CountryId = co.Id AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
	--LEFT JOIN Inventories inv on inv.ArticleId = sales.ArticleId
	--	AND inv.Date = sales.Date
	--	AND inv.StoreId = sales.StoreId
Where
	ar.RangeReviewId = @RangeReviewId
	and ar.IsDeletedPerformance = 0
Group by
	ar.Id
	, ar.Code
	, ar.Name
	, ar.Brand	
	, ar.Delist
	, ar.IsDelisted
	, ar.IsDeletedRangeReview
	, ar.IsNew
	, parentCat.Name
	, cat.Name
	, cat.Code
	, umc.Id
	, umc.Value
	, co.IsVATIncluded
	, am.Margin
	, scm.Margin
	, cm.Margin
	, sales.Date
Order By
	ar.Id, IsPriorYear, sales.Date DESC
